<?php

class m130707_172123_issue_user_and_assignment_tables extends CDbMigration
{
/*
	public function up()
	{
	
	}

	public function down()
	{
		echo "m130707_154904_issue_user_and_assignment_tables does not support migration down.\n";
		return false;
	}
*/
	// Use safeUp/safeDown to do migration with transaction
	public function safeUp()
	{
			//crate Issue table
		$this->createTable('tbl_issue',array(
			'id'=>'pk',
			'name'=>'string NOT NULL',
			'description'=>'text',
			'project_id'=>'int(11) DEFAULT NULL',
			'type_id'=>'int(11) DEFAULT NULL',
			'status_id'=>'int(11) DEFAULT NULL',
			'owner_id'=>'int(11) DEFAULT NULL',
			'requestor_id'=>'int(11) DEFAULT NULL',
			'create_time'=>'datetime DEFAULT NULL',
			'create_user_id'=>'int(11) DEFAULT NULL',
			'update_time'=>'datetime DEFAULT NULL',
			'update_user_id'=>'int(11) DEFAULT NULL'
		), 'Engine=InnoDB');
		
		$this->createTable('tbl_user',array(
			'id'=>'pk',
			'user_name'=>'string NOT NULL',
			'email'=>'string NOT NULL',
			'password'=>'string NOT NULL',
			'last_login_time'=>'datetime DEFAULT NULL',
			'create_time'=>'datetime DEFAULT NULL',
			'create_user_id'=>'int(11) DEFAULT NULL',
			'update_time'=>'datetime DEFAULT NULL',
			'update_user_id'=>'int(11) DEFAULT NULL'
		),'ENGINE=InnoDB');
/*		
		$this->createTable('tbl_project_user_assignment',array(
			'project_id'=>'int(11) NOT NULL',
			'user_id'=>'int(11) NOT NULL',
			'PRIMARY_KEY(`project_id`,`user_id`)'
		),'Engine=InnoDB');
*/		
		//relationship between projects and users
		$this->createTable('tbl_project_user_assignment', array(
		'project_id' => 'int(11) NOT NULL',
		'user_id' => 'int(11) NOT NULL',
		'PRIMARY KEY (`project_id`,`user_id`)',
		), 'ENGINE=InnoDB');
		
		
		//FK Relationships
		//tbl_issue.project_id -> tbl_project.id
		$this->addForeignKey("fk_issue_project","tbl_issue","project_id","tbl_project","id","CASCADE","RESTRICT");
		
		//tbl_issue.owner_id -> tbl_user.id
		$this->addForeignKey("fk_issue_owner","tbl_issue","owner_id","tbl_user","id","CASCADE","RESTRICT");
		
		//tbl_issue.requestor_id ->tbl_user.id
		$this->addForeignKey("fk_issue_requestor","tbl_issue","requestor_id","tbl_user","id","CASCADE","RESTRICT");
		
		//tbl_project_user_assignmnet.projcet.id -> tbl_project.id
		$this->addForeignKey("fk_project_user","tbl_project_user_assignment","project_id","tbl_project","id","CASCADE","RESTRICT");
		
		//tbl_project_user_assignmnet.projcet.id -> tbl_user_id
		$this->addForeignKey("fk_user_project","tbl_project_user_assignment","user_id","tbl_user","id","CASCADE","RESTRICT");

	}

	public function safeDown()
	{
		$this->truncateTable('tbl_user');
		$this->truncateTable('tbl_issue');
		$this->truncateTable('tbl_project_user_assignment');
		$this->dropTable('tbl_user');
		$this->dropTable('tbl_issue');
		$this->dropTable('tbl_project_user_assignment');
	}
}